In [ ]:
import pandas as pd
import matplotlib.pyplot as plt
import descartes
import geopandas as gpd
from shapely.geometry import Point, Polygon
import folium
%matplotlib inline
In [ ]:
df = pd.read_json(path_or_buf="sign_ins_formatted.json")
df
Out[ ]:
| id | createdDateTime | userDisplayName | userPrincipalName | userId | appId | appDisplayName | ipAddress | ipAddressFromResourceProvider | clientAppUsed | ... | authenticationContextClassReferences | authenticationProcessingDetails | networkLocationDetails | authenticationDetails | authenticationRequirementPolicies | sessionLifetimePolicies | privateLinkDetails | appliedEventListeners | authenticationAppPolicyEvaluationDetails | managedServiceIdentity | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | a24a547c-71e2-47fc-97bb-81d7e4d5aa00 | 2024-04-06T16:44:27Z | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | Azure Portal | 69.141.220.21 | NaN | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'}] | [] | [] | [] | [] | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... | |
| 1 | bd5a69d1-0a15-42e0-90da-073377702e00 | 2024-04-06T16:44:24Z | Tyler Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | Azure Portal | 69.141.220.21 | NaN | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'}] | [] | [{'authenticationStepDateTime': '2024-04-06T16... | [] | [] | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'Number Match', 'adminConfigur... | {'msiType': 'none', 'associatedResourceId': No... | |
| 2 | 452d6571-4dba-4ea5-8acf-777ac906b300 | 2024-04-06T16:44:17Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | 4813382a-8fa7-425e-ab75-3b753aab3abb | Microsoft Authenticator App | 69.141.220.21 | NaN | Mobile Apps and Desktop clients | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'},... | [] | [] | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... |
| 3 | c4827fe9-ee3a-4d1e-a320-d166620aa500 | 2024-04-06T16:40:46Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | af124e86-4e96-495a-b70a-90f90ab96707 | OneDrive iOS App | 69.141.220.21 | NaN | Mobile Apps and Desktop clients | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'},... | [] | [] | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... |
| 4 | d87909c1-c089-4336-be59-fe56ce5ba600 | 2024-04-06T16:39:31Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | de8bc8b5-d9f9-48b1-a8ad-b748da725064 | Graph Explorer | 69.141.220.21 | NaN | Browser | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'}] | [] | [] | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 5297 | f495a8aa-2cf1-449e-afed-2579fc408600 | 2024-03-07T18:31:20Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a81d90ac-aa75-4cf8-b14c-58bf348528fe | Microsoft Community v2 | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Browser | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'}] | [] | [{'authenticationStepDateTime': '2024-03-07T18... | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... |
| 5298 | 937f1245-de51-4abb-882c-367019823700 | 2024-03-07T18:31:03Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a81d90ac-aa75-4cf8-b14c-58bf348528fe | Microsoft Community v2 | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Browser | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'}] | [] | [{'authenticationStepDateTime': '2024-03-07T18... | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... |
| 5299 | f495a8aa-2cf1-449e-afed-2579d0358600 | 2024-03-07T18:30:46Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a81d90ac-aa75-4cf8-b14c-58bf348528fe | Microsoft Community v2 | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Browser | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'}] | [] | [{'authenticationStepDateTime': '2024-03-07T18... | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... |
| 5300 | 0890237d-aeae-464d-905b-17908bc7b400 | 2024-03-07T18:28:12Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | af124e86-4e96-495a-b70a-90f90ab96707 | OneDrive iOS App | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Mobile Apps and Desktop clients | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'},... | [] | [] | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... |
| 5301 | 3cb8a923-341a-4d63-8b00-7cc108384f01 | 2024-03-07T18:09:45Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | 27922004-5251-4030-b22d-91ecd9a37ea4 | Outlook Mobile | 108.5.169.55 | NaN | Mobile Apps and Desktop clients | ... | [] | [{'key': 'Root Key Type', 'value': 'Unknown'},... | [] | [] | [] | [{'expirationRequirement': 'rememberMultifacto... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... |
5302 rows × 66 columns
In [ ]:
df.columns.to_list()
Out[ ]:
['id', 'createdDateTime', 'userDisplayName', 'userPrincipalName', 'userId', 'appId', 'appDisplayName', 'ipAddress', 'ipAddressFromResourceProvider', 'clientAppUsed', 'userAgent', 'correlationId', 'conditionalAccessStatus', 'originalRequestId', 'isInteractive', 'tokenIssuerName', 'tokenIssuerType', 'clientCredentialType', 'processingTimeInMilliseconds', 'riskDetail', 'riskLevelAggregated', 'riskLevelDuringSignIn', 'riskState', 'riskEventTypes_v2', 'resourceDisplayName', 'resourceId', 'resourceTenantId', 'homeTenantId', 'homeTenantName', 'authenticationMethodsUsed', 'authenticationRequirement', 'signInIdentifier', 'signInIdentifierType', 'servicePrincipalName', 'signInEventTypes', 'servicePrincipalId', 'federatedCredentialId', 'userType', 'flaggedForReview', 'isTenantRestricted', 'autonomousSystemNumber', 'crossTenantAccessType', 'servicePrincipalCredentialKeyId', 'servicePrincipalCredentialThumbprint', 'uniqueTokenIdentifier', 'incomingTokenType', 'authenticationProtocol', 'resourceServicePrincipalId', 'signInTokenProtectionStatus', 'originalTransferMethod', 'mfaDetail', 'authenticationAppDeviceDetails', 'status', 'deviceDetail', 'location', 'appliedConditionalAccessPolicies', 'authenticationContextClassReferences', 'authenticationProcessingDetails', 'networkLocationDetails', 'authenticationDetails', 'authenticationRequirementPolicies', 'sessionLifetimePolicies', 'privateLinkDetails', 'appliedEventListeners', 'authenticationAppPolicyEvaluationDetails', 'managedServiceIdentity']
In [ ]:
locations = df["location"]
locations[1]["geoCoordinates"]
Out[ ]:
{'altitude': None, 'latitude': 40.35863, 'longitude': -74.13705}
In [ ]:
city = locations.apply(lambda x: x["city"])
state = locations.apply(lambda x: x["state"])
countryOrRegion = locations.apply(lambda x: x["countryOrRegion"])
altitude = locations.apply(lambda x: x["geoCoordinates"]).apply(
lambda x: x["altitude"])
latitude = locations.apply(lambda x: x["geoCoordinates"]).apply(
lambda x: x["latitude"])
longitude = locations.apply(lambda x: x["geoCoordinates"]).apply(
lambda x: x["longitude"]
)
df["city"] = city
df["state"] = state
df["countryOrRegion"] = countryOrRegion
df["altitude"] = altitude
df["latitude"] = latitude
df["longitude"] = longitude
df
Out[ ]:
| id | createdDateTime | userDisplayName | userPrincipalName | userId | appId | appDisplayName | ipAddress | ipAddressFromResourceProvider | clientAppUsed | ... | privateLinkDetails | appliedEventListeners | authenticationAppPolicyEvaluationDetails | managedServiceIdentity | city | state | countryOrRegion | altitude | latitude | longitude | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | a24a547c-71e2-47fc-97bb-81d7e4d5aa00 | 2024-04-06T16:44:27Z | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | Azure Portal | 69.141.220.21 | NaN | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... | None | NaN | NaN | ||||
| 1 | bd5a69d1-0a15-42e0-90da-073377702e00 | 2024-04-06T16:44:24Z | Tyler Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | c44b4083-3bb0-49c1-b47d-974e53cbdf3c | Azure Portal | 69.141.220.21 | NaN | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'Number Match', 'adminConfigur... | {'msiType': 'none', 'associatedResourceId': No... | Lincroft | New Jersey | US | None | 40.35863 | -74.13705 | |
| 2 | 452d6571-4dba-4ea5-8acf-777ac906b300 | 2024-04-06T16:44:17Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | 4813382a-8fa7-425e-ab75-3b753aab3abb | Microsoft Authenticator App | 69.141.220.21 | NaN | Mobile Apps and Desktop clients | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... | Lincroft | New Jersey | US | None | 40.35863 | -74.13705 |
| 3 | c4827fe9-ee3a-4d1e-a320-d166620aa500 | 2024-04-06T16:40:46Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | af124e86-4e96-495a-b70a-90f90ab96707 | OneDrive iOS App | 69.141.220.21 | NaN | Mobile Apps and Desktop clients | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... | Lincroft | New Jersey | US | None | 40.35863 | -74.13705 |
| 4 | d87909c1-c089-4336-be59-fe56ce5ba600 | 2024-04-06T16:39:31Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | de8bc8b5-d9f9-48b1-a8ad-b748da725064 | Graph Explorer | 69.141.220.21 | NaN | Browser | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... | Lincroft | New Jersey | US | None | 40.35863 | -74.13705 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 5297 | f495a8aa-2cf1-449e-afed-2579fc408600 | 2024-03-07T18:31:20Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a81d90ac-aa75-4cf8-b14c-58bf348528fe | Microsoft Community v2 | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Browser | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... | Freehold | New Jersey | US | None | 40.27322 | -74.23018 |
| 5298 | 937f1245-de51-4abb-882c-367019823700 | 2024-03-07T18:31:03Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a81d90ac-aa75-4cf8-b14c-58bf348528fe | Microsoft Community v2 | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Browser | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... | Freehold | New Jersey | US | None | 40.27322 | -74.23018 |
| 5299 | f495a8aa-2cf1-449e-afed-2579d0358600 | 2024-03-07T18:30:46Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | a81d90ac-aa75-4cf8-b14c-58bf348528fe | Microsoft Community v2 | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Browser | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [] | {'msiType': 'none', 'associatedResourceId': No... | Freehold | New Jersey | US | None | 40.27322 | -74.23018 |
| 5300 | 0890237d-aeae-464d-905b-17908bc7b400 | 2024-03-07T18:28:12Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | af124e86-4e96-495a-b70a-90f90ab96707 | OneDrive iOS App | 2600:1001:b112:cbee:8c5f:2fad:5454:f48e | NaN | Mobile Apps and Desktop clients | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... | Freehold | New Jersey | US | None | 40.27322 | -74.23018 |
| 5301 | 3cb8a923-341a-4d63-8b00-7cc108384f01 | 2024-03-07T18:09:45Z | Tyler M. Neher | tyler@neherdata.com | a3bc732b-08dc-4ec2-b327-9dcf0c1eeafa | 27922004-5251-4030-b22d-91ecd9a37ea4 | Outlook Mobile | 108.5.169.55 | NaN | Mobile Apps and Desktop clients | ... | {'policyId': '', 'policyName': '', 'resourceId... | [] | [{'policyName': 'App Lock', 'adminConfiguratio... | {'msiType': 'none', 'associatedResourceId': No... | Hazlet Township | New Jersey | US | None | 40.42598 | -74.16610 |
5302 rows × 72 columns
In [ ]:
worldmap = gpd.read_file(gpd.datasets.get_path("naturalearth_lowres"))
# Creating axes and plotting world map
fig, ax = plt.subplots(figsize=(16, 10))
worldmap.plot(color="lightgrey", ax=ax)
x = df["longitude"]
y = df["latitude"]
plt.scatter(x, y, alpha=0.6, cmap="autumn")
# plt.colorbar(label='NeherData.com Tenant Microsoft Entra ID Login Attempts')
# Creating axis limits and title
plt.xlim([-180, 180])
plt.ylim([-90, 90])
plt.title("NeherData.com Tenant Microsoft Entra ID Login Attempts")
plt.xlabel("Longitude")
plt.ylabel("Latitude")
plt.show()
/var/folders/m4/r3wttq2j5hs_1mpf0t1yvhq80000gn/T/ipykernel_76624/2671292473.py:1: FutureWarning: The geopandas.dataset module is deprecated and will be removed in GeoPandas 1.0. You can get the original 'naturalearth_lowres' data from https://www.naturalearthdata.com/downloads/110m-cultural-vectors/.
worldmap = gpd.read_file(gpd.datasets.get_path("naturalearth_lowres"))
/var/folders/m4/r3wttq2j5hs_1mpf0t1yvhq80000gn/T/ipykernel_76624/2671292473.py:9: UserWarning: No data for colormapping provided via 'c'. Parameters 'cmap' will be ignored
plt.scatter(x, y, alpha=0.6, cmap="autumn")
In [ ]:
# Make an empty map
m = folium.Map(location=[20, 0], tiles="CartoDB Voyager", zoom_start=2)
# Show the map
m
Out[ ]:
Make
this Notebook Trusted to load map: File -> Trust Notebook
In [ ]:
latlon_df = pd.DataFrame(
data=df.dropna(subset=["latitude", "longitude"]),
columns=["latitude", "longitude", "city", "state", "countryOrRegion"],
)
# latlon_df['latitude'] = df['latitude'].notna()
latlon_df
Out[ ]:
| latitude | longitude | city | state | countryOrRegion | |
|---|---|---|---|---|---|
| 1 | 40.35863 | -74.13705 | Lincroft | New Jersey | US |
| 2 | 40.35863 | -74.13705 | Lincroft | New Jersey | US |
| 3 | 40.35863 | -74.13705 | Lincroft | New Jersey | US |
| 4 | 40.35863 | -74.13705 | Lincroft | New Jersey | US |
| 5 | 40.35863 | -74.13705 | Lincroft | New Jersey | US |
| ... | ... | ... | ... | ... | ... |
| 5297 | 40.27322 | -74.23018 | Freehold | New Jersey | US |
| 5298 | 40.27322 | -74.23018 | Freehold | New Jersey | US |
| 5299 | 40.27322 | -74.23018 | Freehold | New Jersey | US |
| 5300 | 40.27322 | -74.23018 | Freehold | New Jersey | US |
| 5301 | 40.42598 | -74.16610 | Hazlet Township | New Jersey | US |
5295 rows × 5 columns
In [ ]:
# add marker one by one on the map
for i in range(0, len(latlon_df)):
folium.Marker(
location=[latlon_df.iloc[i]["latitude"],
latlon_df.iloc[i]["longitude"]],
popup=latlon_df.iloc[i]["city"],
).add_to(m)
# Show the map again
m
Out[ ]:
Make
this Notebook Trusted to load map: File -> Trust Notebook
In [ ]:
map_html = m._repr_html_()